﻿using System;
using System.Windows;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Group_Project_Book_Store.DatabaseClasses
{
    /// <summary>
    /// Managements the query of the database and provide methods for SQL and data manipulation
    /// </summary>
    class DatabaseManagment
    {
        DatabaseAccess dataAccess;
        /// <summary>
        /// ststic list of invoices
        /// </summary>
        static public List<clsInvoice> listOfInvoices = new List<clsInvoice>();
        /// <summary>
        /// static list of books
        /// </summary>
        
        static public List<clsBook> listOfBooks = new List<clsBook>();
        /// <summary>
        /// static list of Payers
        /// </summary>
        
        static public List<clsPayer> listOfPayers = new List<clsPayer>();
        /// <summary>
        /// Static list of Authors
        /// </summary>
        
        static public List<clsAuthors> listOfAuthors = new List<clsAuthors>();
        /// <summary>
        /// static list of Publishers
        /// </summary>
        
        static public List<clsPublisher> listOfPublishers = new List<clsPublisher>();
        /// <summary>
        /// static list of Categories
        /// </summary>
        
        static public List<clsCategory> listOfCategories = new List<clsCategory>();
        /// <summary>
        /// static list of Postal codes
        /// </summary>
        
        static public List<string> lstPostalCodes = new List<string>();
        // <summary>
        /// Constructor 
        /// </summary>
       public DatabaseManagment()
        {
            dataAccess = new DatabaseAccess();
            PopulateAllListOfCategories(); 
            PopulateAllListOfPostalCodes();
            PopulateAllListOfPublishers();
            PopulateAllListOfAuthors();
            PopulateAllListOfPayers();
            PopulateAllListOfBooks(); 
            PopulateAllListOfInvoices();
        }
        /// <summary>
        /// Gets the List of Invoices from the dataBase and Populates the list of Invoices
        /// </summary>
        private void PopulateAllListOfInvoices()
        {
            try
            {
                var query = "SELECT * FROM Invoice";
                DataSet ds = dataAccess.ExecuteSelectQuery(query);
                DataTable results = ds.Tables[0];

                listOfInvoices = results.AsEnumerable()
                .Select(i => new clsInvoice
                    {
                        invoiceID = i.Field<int>("InvoiceID"),
                        invoiceDate = i.Field<DateTime>("InvoiceDate"),
                        invoiceDateEnteredTS = i.Field<DateTime>("DateCreateTS"),
                        payer = SelectPayer(i.Field<int>("PayerID")),
                        listOfBooksInInvoice = SelectBooks(i.Field<int>("InvoiceID")).ToList(),
                    }
                ).ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// Gets the list of Payers from the database
        /// </summary>
        private void PopulateAllListOfPayers()
        {
            try
            {
                var query = "SELECT * FROM Payer ORDER BY PayerName";
                DataSet ds = dataAccess.ExecuteSelectQuery(query);
                DataTable results = ds.Tables[0];
                List<clsPayer> tempListOfPayers = new List<clsPayer>();

                listOfPayers.Add(new clsPayer { payerName = "< New Payer >" });

                tempListOfPayers = results.AsEnumerable().Select(p => new clsPayer
                        {
                        payerID = p.Field<int>("PayerID"),
                        payerName = p.Field<string>("PayerName"),
                        payerAddress = p.Field<string>("Address"),
                        payerCity = p.Field<string>("City"),
                        payerPostalCode = p.Field<string>("PostalCode"),
                        payerPhone = p.Field<string>("Phone"),
                        payerEmail = p.Field<string>("Email"),
                        payerZIP = p.Field<string>("ZIP"),
                    }
                ).ToList();
                listOfPayers.AddRange(tempListOfPayers);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// Gets the list of authors from the database
        /// </summary>
        private void PopulateAllListOfAuthors()
        {
            try
            {
                var query = "SELECT * FROM Authors";
                DataSet ds = dataAccess.ExecuteSelectQuery(query);
                DataTable results = ds.Tables[0];
                listOfAuthors = results.AsEnumerable().Select(p => new clsAuthors
                        {
                            authorID = p.Field<int>("AuthorID"),
                            authorFirstName = p.Field<string>("FirstName"),
                            authorLastName = p.Field<string>("LastName"),
                        }
                    ).ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// gets the list of Publishers from the database
        /// </summary>
        private void PopulateAllListOfPublishers()
        {
            try
            {
                var query = "SELECT * FROM Publisher";
                DataSet ds = dataAccess.ExecuteSelectQuery(query);
                DataTable results = ds.Tables[0];
                listOfPublishers = results.AsEnumerable().Select(p => new clsPublisher
                        {
                            publisherID = p.Field<int>("PublisherID"),
                            publisherName = p.Field<string>("PublisherName"),
                        }
                ).ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// gets a list of books from the database
        /// </summary>
        private void PopulateAllListOfBooks()
        {
            try
            {
                var query = "SELECT * FROM Books";
                DataSet ds = dataAccess.ExecuteSelectQuery(query);
                DataTable results = ds.Tables[0];
                listOfBooks = results.AsEnumerable().Select(b => new clsBook
                        {
                            bookID = b.Field<int>("BookID"),
                            bookTitle = b.Field<string>("BookTitle"),
                            ISBN = b.Field<string>("ISBN"),
                            Publisher = SelectPublisher(b.Field<int>("PublisherID")),
                            Author = SelectAuthor(b.Field<int>("BookID")),
                            categoryType = SelectCategory(b.Field<int>("CategoryID")),
                            bookCost = b.Field<int>("Cost"),
                            itemDateEnteredTS = b.Field<DateTime>("DateEnteredTS"),
                        }
                    ).ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// gets a list of the postal codes from the database
        /// </summary>
        private void PopulateAllListOfPostalCodes()
        {
            try
            {
                var query = "SELECT * FROM PostalCodes";
                DataSet ds = dataAccess.ExecuteSelectQuery(query);
                DataTable results = ds.Tables[0];
                lstPostalCodes = results.AsEnumerable().Select(p => p.Field<string>("PostalCode")).ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// gets a list of categories for the different kinds of books from the database
        /// </summary>
        private void PopulateAllListOfCategories()
        {
            try
            {
                var query = "SELECT * FROM Category";
                DataSet ds = dataAccess.ExecuteSelectQuery(query);
                DataTable results = ds.Tables[0];
                listOfCategories = results.AsEnumerable().Select(p => new clsCategory
                        {
                            typeID = p.Field<int>("TypeID"),
                            categoryName = p.Field<string>("TypeName"),
                            categoryDescription = p.Field<string>("Description"),
                        }
                    ).ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="payerID"></param>
        /// <returns></returns>
        private clsPayer SelectPayer(int payerID)
        {
            try
            {
                var query = "SELECT * FROM Payer WHERE PayerID = " + ClearOutInvalidQueryCharacters(payerID);
                DataSet ds = dataAccess.ExecuteSelectQuery(query);
                DataTable results = ds.Tables[0];
                return results.AsEnumerable().Select(p => new clsPayer 
                        { 
                            payerID = p.Field<int>("PayerID"),
                            payerName = p.Field<string>("PayerName"),
                            payerAddress = p.Field<string>("Address"),
                            payerCity = p.Field<string>("City"),
                            payerPostalCode = p.Field<string>("PostalCode"),
                            payerPhone = p.Field<string>("Phone"),
                            payerEmail = p.Field<string>("Email"),
                            payerZIP = p.Field<string>("ZIP"),
                        }
                    ).FirstOrDefault();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="invoiceID"></param>
        /// <returns></returns>
        private List<clsBook> SelectBooks(int invoiceID) 
        {
            try
            {
                var query = "SELECT * FROM InvoiceBooks ib LEFT JOIN Books b ON ib.BookID = b.BookID WHERE ib.InvoiceID = " + ClearOutInvalidQueryCharacters(invoiceID);
                DataSet ds = dataAccess.ExecuteSelectQuery(query);
                DataTable results = ds.Tables[0];

                return results.AsEnumerable().Select(b => new clsBook
                {
                    bookID = b.Field<int>("b.BookID"),
                    bookTitle = b.Field<string>("BookTitle"),
                    ISBN = b.Field<string>("ISBN"),
                    Publisher = SelectPublisher(b.Field<int>("PublisherID")),
                    Author = SelectAuthor(b.Field<int>("ib.BookID")),
                    categoryType = SelectCategory(b.Field<int>("CategoryID")),
                    bookCost = b.Field<int>("Cost"),
                    itemDateEnteredTS = b.Field<DateTime>("DateEnteredTS"),
                    quantity = b.Field<int>("Quantity"),
                }
                                    ).ToList();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

        }

        #region SelecteMethods
        /// <summary>
        /// method to select the publisher and return it
        /// </summary>
        /// <param name="publisherID"></param>
        /// <returns></returns>
        private clsPublisher SelectPublisher(int publisherID)
        {
            try
            {
                return (from p in listOfPublishers where p.publisherID == publisherID select p).FirstOrDefault();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
        /// <summary>
        /// method to select the author and return the author
        /// </summary>
        /// <param name="authorID"></param>
        /// <returns></returns>
        private clsAuthors SelectAuthor(int authorID)
        {
            try
            {
                return (from a in listOfAuthors where a.authorID == authorID select a).FirstOrDefault();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
        private clsCategory SelectCategory(int categoryID)
        {
            try
            {
                return (from c in listOfCategories where c.typeID == categoryID select c).FirstOrDefault();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
        /// <summary>
        /// return a list of invoices from the database
        /// </summary>
        /// <param name="invoiceID"></param>
        /// <returns></returns>
        public IEnumerable<clsItem> ListOfIemsByInvoiceID()
        {
            try
            {
                return listOfInvoices
                .Select(iv => new clsItem
                {
                    invoiceID = iv.invoiceID,
                    invoiceDate = iv.invoiceDate.ToShortDateString(),
                    payerName = iv.payer.payerName,
                    quantity = iv.listOfBooksInInvoice.Select(b => b.quantity).Sum(),
                    lineTotal = clsHelper.ConvertStringToCurreny(iv.listOfBooksInInvoice.GroupBy(b => b.bookID).Select(b => b.First().bookCost * b.First().quantity).Sum()),
                    invoice = iv,
                }
                    );
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

        }
        /// <summary>
        /// return a list of invoices from the database
        /// </summary>
        /// <param name="invoiceID"></param>
        /// <returns></returns>
        public IEnumerable<clsItem> ListOfIemsByInvoiceID(int invoiceID)
        {
            try
            {
                            return listOfInvoices
                            .Where(i => i.invoiceID == invoiceID)
                            .Select(iv => new clsItem
                                    {
                                        invoiceID = iv.invoiceID,
                                        invoiceDate = iv.invoiceDate.ToShortDateString(),
                                        payerName = iv.payer.payerName,
                                        quantity = iv.listOfBooksInInvoice.Select(b => b.quantity).Sum(),
                                        lineTotal = clsHelper.ConvertStringToCurreny(iv.listOfBooksInInvoice.GroupBy(b => b.bookID).Select(b => b.First().bookCost * b.First().quantity).Sum()),
                                        invoice = iv,
                                    }
                                );
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

        }
        /// <summary>
        /// return a list of totals from the database
        /// </summary>
        /// <param name="invoiceTotal"></param>
        /// <returns></returns>
        public IEnumerable<clsItem> ListOfItemsByTotal(int invoiceTotal)
        {
            try
            {
                return listOfInvoices
                       .Where(i => i.listOfBooksInInvoice.GroupBy(b => b.bookID).Select(b => b.First().bookCost * b.First().quantity).Sum() == invoiceTotal)
                       .Select(iv => new clsItem
                       {
                           invoiceID = iv.invoiceID,
                           invoiceDate = iv.invoiceDate.ToShortDateString(),
                           payerName = iv.payer.payerName,
                           quantity = iv.listOfBooksInInvoice.Count(),
                           lineTotal = clsHelper.ConvertStringToCurreny(iv.listOfBooksInInvoice.GroupBy(b => b.bookID).Select(b => b.First().bookCost * b.First().quantity).Sum()),
                           invoice = iv,
                       }
                               );
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null; 
            }
        }
        /// <summary>
        /// pass in the begin and end dates from the invoice
        /// and return a list of invoices from that date range
        /// </summary>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public IEnumerable<clsItem> ListOfItemsByDateRange(DateTime? beginDate, DateTime? endDate)
        {
            try
            {
                return listOfInvoices
                 .Where(i => i.invoiceDate >= beginDate && i.invoiceDate <= endDate)
                 .Select(iv => new clsItem
                 {
                     invoiceID = iv.invoiceID,
                     invoiceDate = iv.invoiceDate.ToShortDateString(),
                     payerName = iv.payer.payerName,
                     quantity = iv.listOfBooksInInvoice.Count(),
                     lineTotal = clsHelper.ConvertStringToCurreny(iv.listOfBooksInInvoice.GroupBy(b => b.bookID).Select(b => b.First().bookCost * b.First().quantity).Sum()),
                     invoice = iv,
                 }
                     );
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;   
            }
        }
        /// <summary>
        /// select the book and sum of the total
        /// </summary>
        /// <returns></returns>
        public List<string> ListOfTotals()
        {
            try
            {
                return clsHelper.ConvertStringToCurreny(listOfInvoices.Select(iv => iv.listOfBooksInInvoice.GroupBy(b => b.bookID).Select(b => b.First().bookCost * b.First().quantity).Sum()).ToList());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
        #endregion SelecteMethods

        #region InsertMethods

        /// <summary>
        /// Will insert new book
        /// </summary>
        public void InsertNewBook(clsBook book)
        {
            try
            {
                var query = "INSERT INTO Books (BookTitle, ISBN, PublisherID, AuthorID, CategoryID, Cost, DateEnteredTS) VALUES('" 
                    + ClearOutInvalidQueryCharacters(book.bookTitle) + "','"
                    + ClearOutInvalidQueryCharacters(book.ISBN) + "'," 
                    + book.Publisher.publisherID + " ,"
                    + book.Author.authorID + " ,"
                    + book.categoryType.typeID + "," 
                    + ClearOutInvalidQueryCharacters(book.bookCost) + ",'" 
                    + ClearOutInvalidQueryCharacters(DateTime.Now.ToString("MM/dd/yyyy")) + "');";
                dataAccess.ExecuteNonQuery(query);
                PopulateAllListOfBooks();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// Insert new category
        /// </summary>
        /// <param name="category"></param>
        public void InsertNewCategory(clsCategory category)
        { 
            var query="INSERT INTO Category(TypeName, Description ) VALUES ('" 
                + ClearOutInvalidQueryCharacters(category.categoryName) + "','"
                + ClearOutInvalidQueryCharacters(category.categoryDescription) + "');";
            dataAccess.ExecuteNonQuery(query);
            PopulateAllListOfCategories();
        }
        /// <summary>
        /// Delete new category
        /// </summary>
        /// <param name="category"></param>
        public void DeleteBook(clsBook book)
        {
            try
            {
                var query = "DELETE FROM Books WHERE BookID = " + book.bookID;
                dataAccess.ExecuteNonQuery(query);
                PopulateAllListOfBooks();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// Will insert new publisher
        /// </summary>
        public void InsertNewPublisher(clsPublisher publisher)
        {
            try
            {
                if (!listOfPublishers.Select(p => p.publisherName == publisher.publisherName).Any())
                {
                    var query = "INSERT INTO Publisher (PublisherName) VALUES ('" +  ClearOutInvalidQueryCharacters(publisher.publisherName) + "');";
                    dataAccess.ExecuteNonQuery(query);
                    PopulateAllListOfPublishers();    
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        /// <summary>
        /// Will insert new publisher
        /// </summary>
        public void InsertNewAuthor(clsAuthors author)
        {
            try
            {
                if (!listOfAuthors.Select(a => a.authorFirstName == author.authorFirstName && a.authorLastName == author.authorLastName).Any())
                {
                    var query = "INSERT INTO Authors (FirstName, LastName) VALUES ('" + ClearOutInvalidQueryCharacters(author.authorFirstName) + "', '" + author.authorLastName + "');";
                    dataAccess.ExecuteNonQuery(query);
                    PopulateAllListOfAuthors();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void InsertNewInvoice(clsInvoice invoice)
        {
            try
            {
                if (!listOfInvoices.Contains(invoice))
                {
                    var invoiceQuery = "INSERT INTO Invoice (DateCreateTS, InvoiceDate, PayerID) VALUES ('"
                        + ClearOutInvalidQueryCharacters(DateTime.Now.ToString("MM/dd/yyyy")) + "', '"
                        + ClearOutInvalidQueryCharacters(invoice.invoiceDate.ToString("MM/dd/yyyy")) + "',"
                        + ClearOutInvalidQueryCharacters(invoice.payer.payerID) + ");";
                    dataAccess.ExecuteNonQuery(invoiceQuery);
                    var selectQuery = "SELECT * FROM Invoice WHERE "
                        + " DateCreateTS >= " + ClearOutInvalidQueryCharacters(DateTime.Now.ToString("MM/dd/yyyy"))
                        + " AND InvoiceDate >=" + ClearOutInvalidQueryCharacters(invoice.invoiceDate.ToString("MM/dd/yyyy"))
                        + " AND PayerID = " + invoice.payer.payerID + ";";
                    DataSet ds = dataAccess.ExecuteSelectQuery(selectQuery);
                    DataTable dt = ds.Tables[0];
                    int invoiceID = dt.AsEnumerable().Select(i => i.Field<int>("InvoiceID")).FirstOrDefault();
                    if (dt.Rows.Count == 1)
                    {
                        var invoiceBooksQuery = "";
                        foreach (var book in invoice.listOfBooksInInvoice.GroupBy(b => b.bookID))
                        {
                            invoiceBooksQuery = "Insert INTO InvoiceBooks (InvoiceID, BookID, Quantity) VALUES (" 
                                + invoiceID + ", " 
                                + book.First().bookID + ", " 
                                + book.ToList().Count() + ")";
                            dataAccess.ExecuteNonQuery(invoiceBooksQuery);
                        }
                    }
                }
                PopulateAllListOfInvoices();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        public void UpdateBook(clsBook book)
        {
            try
            {
                var query = "UPDATE Books SET "
                    + "BookTitle = '" + ClearOutInvalidQueryCharacters(book.bookTitle)
                    + "', ISBN = '" + ClearOutInvalidQueryCharacters(book.ISBN)
                    + "' , PublisherID = " + book.Publisher.publisherID
                    + ", AuthorID = " + book.Publisher.publisherID
                    + ", CategoryID = " + book.categoryType.typeID
                    + ", Cost = " + ClearOutInvalidQueryCharacters(book.bookCost)
                    + ", DateEnteredTS = " + DateTime.Now.ToString("MM/dd/yyyy")
                    + " WHERE BookID = " + book.bookID;
                dataAccess.ExecuteNonQuery(query);
                PopulateAllListOfBooks();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void UpdateInvoice(clsInvoice invoice)
        {
            try
            {
                var query = "UPDATE Invoice SET "
                    + "DateCreateTS = " + ClearOutInvalidQueryCharacters(DateTime.Now.ToString("MM/dd/yyyy"))
                    + ", InvoiceDate = " + ClearOutInvalidQueryCharacters(invoice.invoiceDate.ToString("MM/dd/yyyy"))
                    + ", PayerID = " + invoice.payer.payerID
                    + " WHERE invoiceID = " + invoice.invoiceID;
                dataAccess.ExecuteNonQuery(query);
                var queryDelete = "DELETE FROM InvoiceBooks WHERE InvoiceID = " + invoice.invoiceID;
                dataAccess.ExecuteNonQuery(queryDelete);
                
                foreach (var book in invoice.listOfBooksInInvoice.GroupBy(b => b.bookID))
                {
                    query = "Insert INTO InvoiceBooks (InvoiceID, BookID, Quantity) VALUES ("
                        + invoice.invoiceID + ", "
                        + book.First().bookID + ", "
                        + book.GroupBy(b => b.bookID).Select(b => b.First().quantity).Sum() + ")";
                    dataAccess.ExecuteNonQuery(query);
                }
                PopulateAllListOfInvoices();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public void DeleteInvoice(clsInvoice invoice)
        {
            try
            {
                var query = "DELETE FROM InvoiceBooks WHERE InvoiceID = "  + invoice.invoiceID;
                dataAccess.ExecuteNonQuery(query);
                query = "DELETE FROM Invoice WHERE InvoiceID = " + invoice.invoiceID;
                dataAccess.ExecuteNonQuery(query);
                PopulateAllListOfInvoices();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public int GetNextInvoiceNumber()
        {
            return (from invoice in DatabaseClasses.DatabaseManagment.listOfInvoices select invoice.invoiceID).ToList().Max() + 1;
        }
        #endregion InsertMethods
        /// <summary>
        /// clears out invalid characters in a string
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        private string ClearOutInvalidQueryCharacters(string s)
        {
            try
            {
                return s.Replace("'", "");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
        /// <summary>
        /// clears out invalid characters in a int
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        private string ClearOutInvalidQueryCharacters(int s)
        {
            try
            {
                 return s.ToString().Replace("'", "");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
        /// <summary>
        /// clears out invalid characters in a datetime
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        private string ClearOutInvalidQueryCharacters(DateTime s)
        {
            try
            {
                return s.ToShortDateString().Replace("'", "");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }
        }
    }
}
